package com.sunyard.layoutit.dao;

import java.io.UnsupportedEncodingException;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.sql.DataSource;
import javax.sql.rowset.serial.SerialBlob;
import javax.sql.rowset.serial.SerialException;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;

import com.sunyard.layoutit.common.DatabaseConfig;
import com.sunyard.layoutit.pojo.GeneralBytesVo;

public class GeneralBytesDao {

	/**
	 * 插入数据
	 * @param vo
	 * @param ds
	 * @return
	 * @throws SerialException
	 * @throws UnsupportedEncodingException
	 * @throws SQLException
	 */
	public int insert(GeneralBytesVo vo,DataSource ds) throws SerialException, UnsupportedEncodingException, SQLException{
		QueryRunner runner=new QueryRunner(ds);
		String sql = "INSERT INTO " + DatabaseConfig.TABLE_PREFIX + "LAYOUTIT_GE_BYTEARRAY(ID_,REV_,NAME_,BYTES_) VALUES(?,?,?,?)" ;
		java.sql.Blob b = new SerialBlob(vo.getBYTES_());
		return runner.update(sql, vo.getID_(),vo.getREV_(),vo.getNAME_(),b);
	}
	
	/**
	 * 插入数据
	 * @param vo
	 * @return
	 * @throws SerialException
	 * @throws UnsupportedEncodingException
	 * @throws SQLException
	 */
	public int insert(GeneralBytesVo vo) throws SerialException, UnsupportedEncodingException, SQLException{
		DataSource ds = DatabaseConfig.getDataSource() ;
		return insert(vo,ds);
	}
	
	public String insert(GeneralBytesVo vo,Connection conn) throws SerialException, UnsupportedEncodingException, SQLException{
		if(null == vo.getID_()){
			String id = String.valueOf(DatabaseConfig.queryNextId("LAYOUTIT_GE_BYTEARRAY"));
			vo.setID_(id);
		}
		QueryRunner runner=new QueryRunner();
		
		String sql = "INSERT INTO " + DatabaseConfig.TABLE_PREFIX + "LAYOUTIT_GE_BYTEARRAY(ID_,REV_,NAME_,BYTES_) VALUES(?,1,?,?)" ;
		//无效的列类型
		int res = runner.execute(conn,sql, vo.getID_(),vo.getNAME_(),vo.getBYTES_());
		
		if(res > 0){
			return vo.getID_();
		}else{
			return null ;
		}
	}
	
	public String insert(String text,String name,Connection conn) throws SerialException, UnsupportedEncodingException, SQLException{
		GeneralBytesVo vo = new GeneralBytesVo();
		String id = String.valueOf(DatabaseConfig.queryNextId("LAYOUTIT_GE_BYTEARRAY"));
		vo.setID_(id);
		if(null == name){
			vo.setNAME_("");
		}else{
			vo.setNAME_(name);
		}
		vo.setBYTES_(text.getBytes(DatabaseConfig.BLOB_CHARSET));
		return insert(vo,conn);
	}
	
	/**
	 * 根据ID查询数据
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public GeneralBytesVo query(String id) throws SQLException{
		String sql = "SELECT T.ID_,T.REV_,T.NAME_,T.BYTES_ FROM " + DatabaseConfig.TABLE_PREFIX + "LAYOUTIT_GE_BYTEARRAY T WHERE T.ID_=?";
		DataSource ds = DatabaseConfig.getDataSource() ;
		QueryRunner runner = new QueryRunner(ds);
		return runner.query(sql, new ResultSetHandler<GeneralBytesVo>(){
			public GeneralBytesVo handle(final ResultSet rs) throws SQLException {
				if(rs.next()){
					final GeneralBytesVo vo = new GeneralBytesVo();
					vo.setID_(rs.getString("ID_"));
					vo.setREV_(rs.getInt("ID_"));
					vo.setNAME_(rs.getString("NAME_"));
					final java.sql.Blob b = rs.getBlob("BYTES_") ;
					vo.setBYTES_(b.getBytes(1, (int)b.length()));
					return vo ;
				}
				return null;
			}
		},id);
	}
	
	/**
	 * 根据ID查询数据
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public GeneralBytesVo query(String id,Connection conn) throws SQLException{
		String sql = "SELECT T.ID_,T.REV_,T.NAME_,T.BYTES_ FROM " + DatabaseConfig.TABLE_PREFIX + "LAYOUTIT_GE_BYTEARRAY T WHERE T.ID_=?";
		QueryRunner runner = new QueryRunner();
		return runner.query(conn,sql, new ResultSetHandler<GeneralBytesVo>(){
			public GeneralBytesVo handle(final ResultSet rs) throws SQLException {
				if(rs.next()){
					final GeneralBytesVo vo = new GeneralBytesVo();
					vo.setID_(rs.getString("ID_"));
					vo.setREV_(rs.getInt("ID_"));
					vo.setNAME_(rs.getString("NAME_"));
					final java.sql.Blob b = rs.getBlob("BYTES_") ;
					vo.setBYTES_(b.getBytes(1, (int)b.length()));
					return vo ;
				}
				return null;
			}
		},id);
	}
	
	/**
	 * 根据ID查询数据
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public String queryText(String id,Connection conn) throws SQLException{
		String sql = "SELECT T.ID_,T.REV_,T.NAME_,T.BYTES_ FROM " + DatabaseConfig.TABLE_PREFIX + "LAYOUTIT_GE_BYTEARRAY T WHERE T.ID_=?";
		QueryRunner runner = new QueryRunner();
		return runner.query(conn,sql, new ResultSetHandler<String>(){
			public String handle(final ResultSet rs) throws SQLException {
				if(rs.next()){
					Blob b = rs.getBlob("BYTES_") ;
					String res;
					try {
						res = new String(b.getBytes(1, (int)b.length()),DatabaseConfig.BLOB_CHARSET);
					} catch (UnsupportedEncodingException e) {
						e.printStackTrace();
						throw new RuntimeException(e);
					}
					return res ;
				}
				return null;
			}
		},id);
	}
}
